500049 Video Annotation and Reference System : SQL Examples

This page last changed on Nov 29, 2006 by brian.

SQL Scripts

Scripts that have been used with VARS can be found in the source code repository

Examples

Retrieving snap times of frame-grabs

Inputs

Fragments of the image URL

Outputs

The datetime that the image was taken

Query Example

SELECT 
    MIN(dbo.Observation.ObservationDTG) AS SnapTime
FROM 
    dbo.VideoFrame INNER JOIN
    dbo.CameraData ON dbo.VideoFrame.id = dbo.CameraData.VideoFrameID_FK INNER JOIN
    dbo.Observation ON dbo.VideoFrame.id = dbo.Observation.VideoFrameID_FK
WHERE 
    (dbo.CameraData.StillImageURL LIKE '%Ventana%2603%00_34_19_13.jpg')

Retrieving the epoch seconds that a video-frame was recorded using dive number and tape time-code

Inputs

Dive number
Tape timecode
ROV name (either Tiburon or Ventana)

Outputs

The date of a video-frame in epoch seconds. This is the date that the video was recorded.

Query Example

SELECT 
    CONVERT(bigint, DATEDIFF(ss, '01-01-1970 00:00:00', dbo.VideoFrame.RecordedDtg)) AS Epoch
FROM 
    dbo.VideoArchiveSet INNER JOIN
    dbo.VideoArchive ON dbo.VideoArchiveSet.id = dbo.VideoArchive.VideoArchiveSetID_FK INNER JOIN
    dbo.VideoFrame ON dbo.VideoArchive.id = dbo.VideoFrame.VideoArchiveID_FK INNER JOIN
    dbo.CameraPlatformDeployment ON dbo.VideoArchiveSet.id = dbo.CameraPlatformDeployment.VideoArchiveSetID_FK
WHERE 
    (dbo.CameraPlatformDeployment.SeqNumber = '2603')
    AND (dbo.VideoFrame.TapeTimeCode = '00:34:19:13')
    AND (dbo.VideoArchiveSet.PlatformName = 'Ventana')

Retrieving the URL's for framegrabs where the observation occured within the last 2 days

Inputs

None

Outputs

The (non-null) image for any observations that were created within the last 2 days

Query Example

SELECT 
    dbo.CameraData.StillImageURL
FROM 
    dbo.CameraData INNER JOIN
    dbo.VideoFrame ON dbo.CameraData.VideoFrameID_FK = dbo.VideoFrame.id INNER JOIN
    dbo.Observation ON dbo.VideoFrame.id = dbo.Observation.VideoFrameID_FK
WHERE 
    (dbo.Observation.ObservationDTG > DATEADD([day], - 2, GETDATE())) AND 
    (NOT (dbo.CameraData.StillImageURL IS NULL))

Query that returns all dive numbers (and vehicles) for any new or updated observation in the last 2 days

Inputs

None

Outputs

The vehicle and dive number for any VideoArchiveSet that has observations add or modified within the last 2 days.

Query Example

SELECT DISTINCT 
    RovName, 
    DiveNumber
FROM 
    Annotations
WHERE 
    (ObservationDate > DATEADD([day], - 2, GETDATE())) AND 
    (NOT (Image IS NULL))